The DataEnvironment Designer

The DataEnvironment designer is one of the most intriguing new features of Visual Basic 6. In short, it's a design-time representation of the ADO objects that you would otherwise create at run time. This capability is very exciting because it brings the same programming paradigm to database development that Visual Basic itself introduced several years ago and that made Windows programming so easy and immediate.

When you use a form designer, you're actually defining at design time the forms and controls Visual Basic will create at run time. You make your choices in a visual manner, without worrying about what Visual Basic actually does when the program runs. Similarly, you can use the DataEnvironment designer to define the behavior of ADO Connections, Commands, and Recordset objects. You can set their properties at design time by pressing the F4 key to bring up the Properties window or by using their custom property pages, exactly as you would do with forms and controls.

The DataEnvironment designer is the descendent of the UserConnection designer, the first external designer ever created for Visual Basic. The UserConnection designer was included in the Enterprise Edition of Visual Basic 5 and could work exclusively with RDO connections, so only a fraction of Visual Basic developers ever used it or even were aware that a such a tool existed. The DataEnvironment designer is much more powerful than its ancestor. It works with any local and remote ADO connection and even supports multiple connections. Moreover, it qualifies as an ADO data source, so you can bind fields to it, as I'll show later.

Another advantage of using DataEnvironment objects defined at design time instead of ADO objects built through code is that—similarly to forms—a DataEnvironment instance is a self-sufficient entity that contains other objects and the code to manage them. You can add public properties and methods to DataEnvironment designers, which greatly improves their reusability, as if they were class modules specialized to work with databases. I believe that DataEnviroment objects, properly used, will revolutionize the way database applications are built.

To add a DataEnvironment designer to the current project, you can choose the Add Data Environment command from the Project menu. This command appears only if you've added a reference to the Microsoft Data Environment Instance 1.0 library. You can also create a DataEnvironment designer from the DataView window. Finally, you can create a new Data Project from the project gallery. In this case, Visual Basic creates a project for you with all the necessary references and an instance of the DataEnvironment designer.

The main object in a DataEnviroment designer is the Connection object. It broadly corresponds to the form object in the Form designer in the sense that it's the top-level object. Unlike forms, however, a DataEnvironment designer instance can contain multiple Connection objects.

You can create a Connection in many ways. When you create a DataEnvironment, it already contains a default Connection object, so you simply need to set its properties. You do this either by pressing F4 to display the standard Properties window, or (better) by right-clicking on the object and selecting the Properties menu command to display its custom property pages. (You get the same effect by clicking on the Properties button on the DataEnviroment toolbar.) I won't spend any time describing the Connection object's property pages because you're already familiar with them. The Provider, Connection, Advanced, and All pages are exactly the same ones that you encountered when setting data link's properties in the DataView window or when creating the ConnectionString property of an ADO Data control.

The standard Properties window contains a few properties that don't appear in the custom property pages. The DesignUserName and DesignPassword properties let you set the user name and password you want to use when you're creating the DataEnvironment object, while RunUserName and RunPassword are the user name and password you want to use when the program is executing. For example, you might develop the application using an Administrator identity and then check how the application behaves at run time when a guest user logs in. You can decide whether you want to see the prompt when the connection opens, and you can use different settings for design time and run time. The DesignPromptBehavior and RunPromptBehavior properties can take the following values: 1-adPromptAlways (always show the login dialog box, so the user is allowed to change login data), 2-adPromptComplete (show the login dialog box only if one or more required parameters are missing), 3-adPromptCompleteRequired (like the previous one, but allow user to enter only required parameters), and 4-adPromptNever (never show the login dialog box, and return an error to the application if one or more required parameters are missing). You usually set DesignPromptBehavior to adPromptComplete and RunPromptBehavior to adPromptNever; the latter prevents malicious users from logging on to other data sources or entering random user names and passwords until they manage to get into the system. Finally, the DesignSaveAuthentication and RunSaveAuthentication properties determine whether the login information described previously is saved in the VBP or the EXE file, respectively. A word of caution is in order here: User names and passwords in EXE files aren't encrypted, so determined hackers might load the file into a hex editor or parse it in some other way until they find that information.

A Command object in the DataEnvironment designer represents an action performed on a database. A command object is always a child of a Connection object, in much the same way a control is always a child of a form. More precisely, you can create a stand-alone Command object, but you can't use it until you make it a child of a Connection object.

The easiest way to create a Command object is by dragging a table, a view, or a stored procedure from the DataView window into the DataEnvironment window. Visual Basic then creates the Command object that corresponds to that table, view, or stored procedure, and it also creates a parent Connection, if necessary. A Command object can be a child only of a Connection that refers to its own database. You can also create one or more Command objects that map to stored procedures in a database by clicking on the Insert Stored Procedures button on the DataEnvironment toolbar. I used this shortcut to quickly create the Command objects visible in Figure 8-19.

There are two kinds of Command objects: ones that return Recordsets and ones that don't. The former are SQL queries, stored procedures, tables, or views that return a Recordset (which can be empty, if no records in the database meet the selection criteria). The latter are SQL commands or stored procedures that insert, delete, or modify values in the database but don't return a set of records. For example, you can create a Command named AuthorsInCA that returns all the authors that live in California by using the following SQL query:

SELECT * FROM Authors WHERE State = 'CA'

Figure 8-19. You can drag tables, views, and stored procedures from the DataView window to the DataEnvironment designer to create Command objects, and right-click on them to display the custom property pages.

Unlike Connection objects, all the properties of a Command object can be set in its custom property pages, and you never need to display the standard Properties window. In the General tab, you select the database object that the Command corresponds to—a table, a view, a stored procedure, or a synonym—or you enter the SQL text of a query. (You can also run the SQL Query Builder to build the query interactively.)

If you have a normal, nonparameterized and nonhierarchical command, you can skip all the intermediate tabs and go to the Advanced page, shown in Figure 8-20. Here you decide the cursor type and location, the type of locking to be enforced, the size of the local cache (that is, the number of records read from the server when necessary), the timeout for the command, and the maximum number of records that the query should return. You can use this last value to prevent a query from returning hundreds of thousands of records and so bringing your workstation and your network to their knees. Don't worry if you don't understand the real meaning of most of these options; they directly map to properties of ADO Recordset and Command objects, so their purpose will be clear to you after you read Chapters 13 and 14.

Figure 8-20. The Advanced tab of the Command's property pages.

The only attribute in this page that doesn't directly relate to an ADO property is the Recordset Returning check box. Most of the time, the DataEnvironment designer is able to determine whether you've added a Recordset returning or non-Recordset returning command, but if it makes a wrong assumption you can correct it by acting on this check box.

Using parameters adds a lot of flexibility to Command objects. You can create two types of parameterized Command objects: those based on a SQL query and those based on a stored procedure with parameters. For the first kind, you must enter a parameterized SQL query, using question marks as placeholders for parameters. For example, you can create a Command object named AuthorsByState, which corresponds to the following query:

SELECT * FROM Authors WHERE State = ?

After you've entered this query in the General tab of the Properties dialog box, switch to the Parameters tab and check that the DataEnvironment has correctly determined that the query embeds one parameter. In this tab, you can assign a name to each parameter, set its data type and size, and so on. All parameters in this type of query are input parameters.

To create a Command object that maps a stored procedure, you can click on the Insert Stored Procedure button and select the stored procedure you're interested in. The DataEnvironment is usually able to retrieve the stored procedure syntax and correctly populate the Command's Parameters collection. You should pay attention to the direction of the parameters because sometimes the DataEnvironment doesn't correctly recognize output parameters and you have to manually fix their Direction attribute. Also, double-check that all string parameters have nonzero sizes.

DataEnvironment designers can work as ADO data sources, so they appear in the DataSource combo box in the Properties window at design time. When you bind a control to a DataEnvironment designer, however, you must also set the DataMember property of a data-aware control to the name of the specific Command object you're binding it to. Only recordset-returning Command objects can work as data sources.

You don't need to manually create controls on a form and bind them to the DataEnvironment object because Visual Basic 6 allows you to do everything with drag-and-drop. To see how simple it is to use this feature, open a new form, click on a Command object in the DataEnvironment window, and drop it on the form. You'll immediately see the form being populated with many TextBox and (possibly) CheckBox controls, one for each field in the Command object, as shown in Figure 8-21. You can press F5 to check that the data binding mechanism is working correctly.

Figure 8-21. A group of bound controls created by dropping a Command object on a form.

Because you don't have a Data control on the form, you must provide the navigational buttons yourself. This is really easy: Just create two CommandButton controls, name them cmdPrevious and cmdNext, and then add the code you see here.

Private Sub cmdNext_Click() DataEnvironment1.rsAuthors.MoveNext End Sub Private Sub cmdPrevious_Click() DataEnvironment1.rsAuthors.MovePrevious End Sub

The preceding code works because the DataEnvironment creates at run time, for each recordset-returning Command object, a Recordset whose name is rs followed by the Command's name. Using the same method, you can add buttons for deleting and inserting records, finding values, and so on. See Chapters 13 and 14 for all the properties, methods, and events of the ADO Recordset object.

You aren't restricted to creating simple controls, and you can even use bound grids to display a tabular view of your records. If you want to use bound grids, you must start the drag-and-drop operation using the right mouse button, release the button when the cursor is over the form, and select the Data Grid option from the pop-up menu. The sample application shown in Figure 8-22 demonstrates how you can use the parameterized AuthorsByState command to display a subset of all the records in the grid. This is the code behind the Filter push button:

Private Sub cmdFilter_Click() ' Run the query, passing the expected "State" parameter. DataEnvironment1.AuthorsByState txtState ' Ensure that the grid is bound to the DataEnvironment. Set DataGrid1.DataSource = DataEnvironment1 DataGrid1.DataMember = "AuthorsByState" End Sub

Or you can bind the grid directly to the Recordset produced by the parameterized query:

Set DataGrid1.DataSource = DataEnvironment1.rsAuthorsByState

Figure 8-22. The sample application shows a record-view and table-view of the Authors table, and it lets you filter records on their State fields.

When you drop a Command object (or an individual database field) onto a form, the DataEnvironment designer creates by default TextBox controls for all types of fields, except CheckBox controls for Boolean fields. You can change this default behavior in the following ways:

• Click on the Options button on the DataEnvironment toolbar to bring up the dialog box shown in Figure 8-23, where you can select which control will be created when you drop a field of a given type. ADO field types are grouped by category, but you can tick the Show All Data Types check box to view individual data types. For each field type, you can select the corresponding control among all the intrinsic Visual Basic controls and all the ActiveX controls installed on the machine. The Drag And Drop Field Captions check box determines whether the DataEnvironment designer will also create Label controls for each field.



Figure 8-23. The Field mapping tab of the Options dialog box lets you select which type of control will be created when you drop a field on a form.

• You can choose from two special field types. The Caption element lets you specify which control will be used to label the other fields. (The default is a Label control.) Multiple is the control to use when you drag a Command object using the left mouse button. You can specify a grid-like control if you want, but leaving the default value (TextBox control) won't hurt because you can always drop either a DataGrid or a Hierarchical FlexGrid control if you start dragging with the right mouse button.
• For maximum flexibility, you can select the control to be used for each individual field of a particular Command object. Just right-click on a field in the DataEnvironment designer, issue the Properties menu command, and then select the control type and the caption to be used when the user drops that field on a form.

The DataEnvironment designer offers a design-time interface to one of the most powerful features of ADO, the ability to create hierarchical Recordsets. A hierarchical Recordset contains a set of records, which in turn might contain other child Recordsets. A practical example will clarify why this is a good thing. Let's say that you want to create a list of authors from the Biblio.mdb database, and for each author you want to display (and possibly update) the list of titles he or she has written. You can retrieve this information by executing an SQL JOIN query, or you can manually populate a form that shows this master/detail relationship by executing a distinct SQL SELECT query on the Title Author and Titles tables each time the user moves to a new record from the main Authors table. But neither of these approaches seems particularly satisfying, especially now that you can use a hierarchical Recordset instead. Let me show you how it works.

You can create hierarchical Recordsets inside a DataEnvironment designer in a couple of different ways. The first one requires that you display the Relation property page of the Command object that corresponds to the main table in the relationship. To see how this technique works, open a connection to the Biblio.mdb database in the DataView window, and then drag its Authors and Title Author tables to the DataEnvironment window. To make the latter Command a child of the former one, display the Title_Author object's property pages and switch to the Relation tab. (See Figure 8-24.)

Click on the Relate To a Parent Command Object check box to activate the controls on this page, and then select the parent Command (Authors in this case) in the combo box. In the Relation Definition frame, you select the fields through which the two Command objects are related. These fields are the primary key in the parent Command and a foreign key in the child Command. In this particular example, the two fields have the same name in both tables, but this isn't generally the case. To complete our example, ensure that the Au_ID item is highlighted in both combo boxes, click on the Add button to add to the list of fields, and then click on the OK button to confirm. You will notice that the Title_Author Command has become a child of the Author Command, at the same level as the latter's fields. In fact, when the DataEnvironment designer creates this hierarchical Recordset at run time, its fourth field will actually contain the child Recordset.

Figure 8-24. The Relation tab of the Properties dialog box.

To refine the example, we need to create a Titles Command and make it a child of the Title_Author object. This time we'll follow a different approach: Right-click on the Title_Author Command, and select the Add Child Command object. This creates an object called Command1 object. Rename it as Titles, bring up its property pages, specify that it takes its records from the Authors table in Biblio.mdb, and then switch to the Relation page to complete the relationship. The Title_Author and Titles Commands are related through their ISBN field, so you can click on the Add button and close the dialog box. You have completed the creation of a 3-level hierarchical Recordset.

To test-drive this new object, create a new form, drag the Authors Command onto it using the right mouse button, and then select the Hierarchical Flex Grid from the menu. This creates an instance of the Microsoft Hierarchical FlexGrid control on the form. Before running the program, you need to make a few columns invisible, so you should right-click on the grid, select the Retrieve Structure command, right-click again to display the Properties dialog box, and switch to the Bands tab. In this tab, you can define which fields are visible for each of the three Recordsets that participate in the relationship. In Band 0 (Authors), clear the Au_ID check box; in Band 1 (Title_Author), clear both the ISBN and Au_ID check boxes (which makes the band invisible); and in Band 2 (Titles), clear the Pub_ID, Description, Notes, and Comments check boxes. You can now run the application, which should display what appears in Figure 8-25. Notice how you can expand and collapse rows using the plus and minus symbols near the grid's left border.

You can better this example by adding another level to the hierarchy, which you'd need to display information about the publisher of each title. You can add the level by dragging the Publishers table from the DataView window to the DataEnvironment designer, making it a child Command of the Titles Command. I leave this to you as an exercise.

Figure 8-25. The Hierarchical FlexGrid control displays hierarchical Recordsets and lets you expand and collapse individual rows in the parent Recordset.

The DataEnvironment designer supports two more types of hierarchies, the Grouping and the Aggregate hierarchies. A grouping hierarchy is conceptually simple: You start with a Command object and build a parent Command that groups the records of the original Command according to one or more fields. To see what this means in practice, drop the Titles table on the DataEnvironment window, bring up its Grouping property page, tick the Group Command Object check box to enable the controls on this tab, move the Year Published field from the left list box to the right list box, and then close the dialog box to confirm your changes. You'll see that the DataEnvironment designer now hosts a new Command object under the main Connection, named Titles1_grouped_using_Title1_Grouping, and two folders under it. One folder contains the Year Published field (the summary field), the other folder contains the fields of the original Titles1 Command. If you bind a Hierarchical FlexGrid control to the new parent Command, you'll see that the leftmost column displays different year numbers and all the other columns contain information about the titles published in that year.

An aggregate field is a calculated field that computes an elementary expression (the count, the sum, the average, and so on) for a given field in all the rows of a Recordset. You often add an aggregate field when you already have a grouping hierarchy. To our previous example, we could add a TitleCount field that reports the number of books that were published in each year. In a more complex example, you might have all your orders grouped by month, with several aggregate fields reporting the order count, the sum of orders total, their average amounts, and so on.

You define aggregate fields in the Aggregates tab of the Properties dialog box. Click on the Add button to create a new aggregate, give it a meaningful name, and select a function among those available: Count, Sum, Average, Minimum, Maximum, Standard Deviation, or Any. (Any returns the value common to all the fields in the selected records.) The Aggregate On combo box determines on which fields the aggregate field is evaluated and can be one of the following: Grouping, Grand Total, or the name of a child Command. (The actual content of the combo box depends on the type of the current Command.) If you select Grand Total, you can enter the name of the grand total field. In this case, a new folder is created under the main Command, and it will gather all the grand total fields in the Command. (See Figure 8-26.)

Figure 8-26. A Command object that exploits all three types of hierarchies.